package com.xmy.cultivate.mapper;

import com.xmy.cultivate.entity.TeacherLeadsYearClass;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 老师带班年级 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2025-02-25
 */
public interface TeacherLeadsYearClassMapper extends BaseMapper<TeacherLeadsYearClass> {



    @Select({"SELECT",
            "t.id,",
            "t.teacher_id,",
            "t.year_class_id,",
            "t.created_id,",
            "d.name AS year_class_name",
            "FROM",
            "teacher_leads_year_class t",
            "LEFT JOIN dict d ON t.year_class_id = d.num ",
            "WHERE",
            "d.type = 14 AND teacher_id =  ${id} ",
            })
    public List<TeacherLeadsYearClass> getTeacherLeadsYearClass(@Param("id") String id);

    @Select({"SELECT",
            "teacher_id,",
            "GROUP_CONCAT(year_class_id) AS year_class_ids,",
            "GROUP_CONCAT(subjects_id) AS subjects_id",
            "FROM (",
            "    SELECT",
            "        t01.*",
            "    FROM (",
            "        SELECT",
            "            t1.id,",
            "            t1.NAME,",
            "            t2.id AS quarter_id,",
            "            t1.teacher_id,",
            "            t1.year_class_id,",
            "            t1.subjects_id",
            "        FROM",
            "            grade AS t1",
            "            INNER JOIN quarter AS t2 ON t1.year_part = t2.year_part",
            "            AND t1.quarter_num = t2.num",
            "        WHERE",
            "            t1.deleted = 0",
            "    ) AS t01",
            "    INNER JOIN (",
            "        SELECT",
            "            t1.id,",
            "            t1.NAME,",
            "            MAX(t2.id) AS quarter_id,",
            "            t1.teacher_id",
            "        FROM",
            "            grade AS t1",
            "            INNER JOIN quarter AS t2 ON t1.year_part = t2.year_part",
            "            AND t1.quarter_num = t2.num",
            "        WHERE",
            "            t1.deleted = 0",
            "        GROUP BY",
            "            t1.teacher_id",
            "    ) AS t02 ON t01.teacher_id = t02.teacher_id",
            "    AND t01.quarter_id = t02.quarter_id",
            "    GROUP BY",
            "        teacher_id,",
            "        year_class_id,",
            "        subjects_id",
            ") AS t",
            "WHERE t.teacher_id = #{id}",
            "GROUP BY teacher_id"
    })
    TeacherLeadsYearClass getAutoYearClassList(@Param("id") String id);



}
